1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Public Class frmCompany
5
6 Sub Reset()
7 txtSTNo.Text = ""
8 txtTIN.Text = ""
9 txtEmailID.Text = ""
10 txtContactNo.Text = ""
11 txtCompanyName.Text = ""
12 txtCIN.Text = ""
13 txtAddress.Text = ""
14 PictureBox1.Image = My.Resources.Company1
15 txtCompanyName.Focus()
16 btnSave.Enabled = True
17 btnUpdate.Enabled = False
18 btnDelete.Enabled = False
19 End Sub
20 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
21 Me.Close()
22 End Sub
23
24 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
25 If txtCompanyName.Text = "" Then
26 MessageBox.Show("Please enter company name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
27 txtCompanyName.Focus()
28 Return
29 End If
30 If txtAddress.Text = "" Then
31 MessageBox.Show("Please enter address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
32 txtAddress.Focus()
33 Return
34 End If
35 If txtContactNo.Text = "" Then
36 MessageBox.Show("Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
37 txtContactNo.Focus()
38 Return
39 End If
40
41 If txtEmailID.Text = "" Then
42 MessageBox.Show("Please enter email id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
43 txtEmailID.Focus()
44 Return
45 End If
46 Try
47 con = New SqlConnection(cs)
48 con.Open()
49 Dim ct As String = "select count(*) from company Having count(*) >= 1"
50 cmd = New SqlCommand(ct)
51 cmd.Connection = con
52 rdr = cmd.ExecuteReader()
53 If rdr.Read Then
54 MessageBox.Show("Record Already Exists" & vbCrLf & "please update the company info", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
55 If Not rdr Is Nothing Then
56 rdr.Close()
57 End If
58 Exit Sub
59 End If
60 con = New SqlConnection(cs)
61 con.Open()
62
63 Dim cb As String = "insert into company(companyName, Address, ContactNo, EmailID, TIN, STNo, CIN, Logo) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8)"
64 cmd = New SqlCommand(cb)
65 cmd.Connection = con
66 cmd.Parameters.AddWithValue("@d1", txtCompanyName.Text)
67 cmd.Parameters.AddWithValue("@d2", txtAddress.Text)
68 cmd.Parameters.AddWithValue("@d3", txtContactNo.Text)
69 cmd.Parameters.AddWithValue("@d4", txtEmailID.Text)
70 cmd.Parameters.AddWithValue("@d5", txtTIN.Text)
71 cmd.Parameters.AddWithValue("@d6", txtSTNo.Text)
72 cmd.Parameters.AddWithValue("@d7", txtCIN.Text)
73 Dim ms As New MemoryStream()
74 Dim bmpImage As New Bitmap(PictureBox1.Image)
75 bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
76 Dim data As Byte() = ms.GetBuffer()
77 Dim p As New SqlParameter("@d8", SqlDbType.Image)
78 p.Value = data
79 cmd.Parameters.Add(p)
80 cmd.ExecuteNonQuery()
81 con.Close()
82 Dim st As String = "added the company '" & txtCompanyName.Text & "' info"
83 LogFunc(lblUser.Text, st)
84 MessageBox.Show("Successfully saved", "Company Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
85 btnSave.Enabled = False
86 Getdata()
87 Catch ex As Exception
88 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
89 End Try
90 End Sub
91
92
93 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
94 If txtCompanyName.Text = "" Then
95 MessageBox.Show("Please enter company name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
96 txtCompanyName.Focus()
97 Return
98 End If
99 If txtAddress.Text = "" Then
100 MessageBox.Show("Please enter address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
101 txtAddress.Focus()
102 Return
103 End If
104 If txtContactNo.Text = "" Then
105 MessageBox.Show("Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
106 txtContactNo.Focus()
107 Return
108 End If
109
110 If txtEmailID.Text = "" Then
111 MessageBox.Show("Please enter email id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
112 txtEmailID.Focus()
113 Return
114 End If
115 Try
116
117 con = New SqlConnection(cs)
118 con.Open()
119 Dim cb As String = "Update company set companyName=@d1, Address=@d2, ContactNo=@d3, EmailID=@d4, TIN=@d5, STNo=@d6, CIN=@d7, Logo=@d8 where ID=" & txtID.Text & ""
120 cmd = New SqlCommand(cb)
121 cmd.Connection = con
122 cmd.Parameters.AddWithValue("@d1", txtCompanyName.Text)
123 cmd.Parameters.AddWithValue("@d2", txtAddress.Text)
124 cmd.Parameters.AddWithValue("@d3", txtContactNo.Text)
125 cmd.Parameters.AddWithValue("@d4", txtEmailID.Text)
126 cmd.Parameters.AddWithValue("@d5", txtTIN.Text)
127 cmd.Parameters.AddWithValue("@d6", txtSTNo.Text)
128 cmd.Parameters.AddWithValue("@d7", txtCIN.Text)
129 Dim ms As New MemoryStream()
130 Dim bmpImage As New Bitmap(PictureBox1.Image)
131 bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
132 Dim data As Byte() = ms.GetBuffer()
133 Dim p As New SqlParameter("@d8", SqlDbType.Image)
134 p.Value = data
135 cmd.Parameters.Add(p)
136 cmd.ExecuteNonQuery()
137 con.Close()
138 Dim st As String = "updated the company '" & txtCompanyName.Text & "' info"
139 LogFunc(lblUser.Text, st)
140 MessageBox.Show("Successfully updated", "Company Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
141 btnUpdate.Enabled = False
142 Getdata()
143 Catch ex As Exception
144 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
145 End Try
146 End Sub
147 Public Sub Getdata()
148 Try
149 con = New SqlConnection(cs)
150 con.Open()
151 cmd = New SqlCommand("SELECT RTRIM(ID), RTRIM(companyName), RTRIM(Address), RTRIM(ContactNo), RTRIM(EmailID), RTRIM(TIN), RTRIM(STNo), RTRIM(CIN), Logo from company", con)
152 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
153 dgw.Rows.Clear()
154 While (rdr.Read() = True)
155 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8))
156 End While
157 con.Close()
158 Catch ex As Exception
159 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
160 End Try
161 End Sub
162 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
163 Reset()
164 End Sub
165
166
167 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
168 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
169 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
170 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
171 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
172 End If
173 Dim b As Brush = SystemBrushes.ControlText
174 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
175
176 End Sub
177
178 Private Sub frmRegistration_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
179 Getdata()
180 End Sub
181
182 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
183 Try
184
185 If dgw.Rows.Count > 0 Then
186 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
187 txtID.Text = dr.Cells(0).Value.ToString()
188 txtCompanyName.Text = dr.Cells(1).Value.ToString()
189 txtAddress.Text = dr.Cells(2).Value.ToString()
190 txtContactNo.Text = dr.Cells(3).Value.ToString()
191 txtEmailID.Text = dr.Cells(4).Value.ToString()
192 txtTIN.Text = dr.Cells(5).Value.ToString()
193 txtSTNo.Text = dr.Cells(6).Value.ToString()
194 txtCIN.Text = dr.Cells(7).Value.ToString()
195 Dim data As Byte() = DirectCast(dr.Cells(8).Value, Byte())
196 Dim ms As New MemoryStream(data)
197 Me.PictureBox1.Image = Image.FromStream(ms)
198 btnUpdate.Enabled = True
199 btnSave.Enabled = False
200 btnDelete.Enabled = True
201 End If
202 Catch ex As Exception
203 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
204 End Try
205 End Sub
206
207 Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
208 Try
209 With OpenFileDialog1
210 .Filter = ("Images |*.png; *.bmp; *.jpg;*.jpeg; *.gif;*.ico;")
211 .FilterIndex = 4
212 End With
213 'Clear the file name
214 OpenFileDialog1.FileName = ""
215 If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
216 PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
217 End If
218 Catch ex As Exception
219 MsgBox(ex.ToString())
220 End Try
221 End Sub
222
223 Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
224 Try
225 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
226 DeleteRecord()
227 End If
228 Catch ex As Exception
229 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
230 End Try
231 End Sub
232 Private Sub DeleteRecord()
233
234 Try
235
236 Dim RowsAffected As Integer = 0
237 con = New SqlConnection(cs)
238 con.Open()
239 Dim cq As String = "delete from company where ID=@d1"
240 cmd = New SqlCommand(cq)
241 cmd.Connection = con
242 cmd.Parameters.AddWithValue("@d1", txtID.Text)
243 RowsAffected = cmd.ExecuteNonQuery()
244 If RowsAffected > 0 Then
245 Dim st As String = "deleted the company '" & txtCompanyName.Text & "'"
246 LogFunc(lblUser.Text, st)
247 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
248 Getdata()
249 Reset()
250 Else
251 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
252 Reset()
253 End If
254 If con.State = ConnectionState.Open Then
255 con.Close()
256
257 End If
258 Catch ex As Exception
259 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
260 End Try
261 End Sub
262 End Class
